﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Mail_Delete_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Mail_Delete_V2];
GO

CREATE PROCEDURE [dbo].[sproc_Mail_Delete_V2]
(
    @mailIdXml      xml,
    @physicalRemove int = 0 -- move to recyble bin or physically remove this email.   
)
AS
BEGIN

SET NOCOUNT ON;
SET XACT_ABORT ON;

IF ( @@TRANCOUNT = 0 )
BEGIN
    RAISERROR(N'This sproc must be executed within an active transaction.', 16, 1);
    RETURN -1;
END

/*
 * XML format
 * <mails>
 *  <m id="" />
 * </mails>
 */
DECLARE @tmp TABLE
(
    MailID int NOT NULL
);
INSERT INTO @tmp(MailID)
    SELECT DISTINCT X.r.value(N'@id', N'int')
    FROM @mailIdXml.nodes(N'/mails/m') X(r);

IF ( @physicalRemove = 0 )
BEGIN
    -- Just move to recyble bin
    UPDATE M
        SET M.FolderType = /* recycle bin */ 3
        FROM @tmp T INNER JOIN dbo.UDS_Mail M ON T.MailID = M.MailID
END
ELSE
BEGIN
    DECLARE @deletedMailBody TABLE
    (
        BodyID int NOT NULL
    );

    DELETE M FROM dbo.UDS_Mail M INNER JOIN @tmp T ON T.MailID = M.MailID;
    
    -- Remove the mail body of no one owns the mail
    DELETE
        FROM dbo.UDS_MailBody
        OUTPUT DELETED.MailBodyID INTO @deletedMailBody
        WHERE MailBodyID NOT IN (
            SELECT MailBodyID FROM dbo.UDS_Mail
        );
    
    -- Remove the attachment records from DB, 
    -- and return the deleted records so that the managed code can remove the physical files
    DELETE MA
        OUTPUT DELETED.*
        FROM dbo.UDS_MailAttachment MA INNER JOIN @deletedMailBody DM ON MA.MailBodyID = DM.BodyID
END

END
GO